﻿using System;
using System.Collections.Generic;
using System.Linq;
using DMS.Data;
using DMS.Data.Modules.Statistics;
using DMS.Business.BaseClass;
using DMS.Business.Utility;
using System.Data;
using DMS.Base;
using DMS.Business.Modules.Organization;
using DMS.Business.Modules.SupplierManagement;

namespace DMS.Business.Modules.Statistics
{
    /// <summary>
    /// 统计服务
    /// </summary>
    public class StatService : BaseService
    {
        /// <summary>
        /// 按大部门统计总配送数量、总配送金额、总配送金额、签收量、退件量
        /// </summary>
        /// <param name="actor">参与者</param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataSet StatByBigDept(Actor actor,string condition)
        {
            //权限过滤
            condition += GetLimitCondition(actor);

//            string sql = string.Format(@"
//select t1.UnitID,t1.Unit,t1.Quantity,t2.ReceivedQuantity,t3.RejectedQuantity
//from
//(select ApplierBigDeptID as UnitID,ApplierBigDept as Unit,count(0) as Quantity
//from PS_Orders
//where IsDeleted=0 and InitialTime is not null 
//{0}
//group by ApplierBigDeptID,ApplierBigDept)t1
//left join
//(select ApplierBigDeptID as UnitID,ApplierBigDept as Unit,count(0) as ReceivedQuantity
//from PS_Orders
//where IsDeleted=0 and ArriveTime is not null and IsReceived = 1 {0}
//group by ApplierBigDeptID,ApplierBigDept)t2
//on t1.UnitID=t2.UnitID
//left join
//(select ApplierBigDeptID as UnitID,ApplierBigDept as Unit,count(0) as RejectedQuantity
//from PS_Orders
//where IsDeleted=0 and ArriveTime is not null and IsReceived = 0 {0}
//group by ApplierBigDeptID,ApplierBigDept)t3
//on t1.UnitID=t3.UnitID
//order by t1.Unit", condition);

            DataSet ds = null;//DBHelper.Instance.ExecuteDataSet(CommandType.Text,sql);
            return ds;
        }

        /// <summary>
        /// 根据父部门往下钻
        /// </summary>
        /// <param name="parentDeptID"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataSet StatByParentDept(Actor actor,string parentDeptID, string condition)
        {
            //权限过滤
            condition += GetLimitCondition(actor);

            string sql = "";

            //先统计父部门中的人
            sql = string.Format(@"
select t1.UnitID,t1.Unit,t1.Quantity,t2.ReceivedQuantity,t3.RejectedQuantity,1 as UnitType
from
(select ApplierID as UnitID,Applier as Unit,count(0) as Quantity
from PS_Orders
where IsDeleted=0 and InitialTime is not null and ApplierDeptID='{0}' 
{1}
group by ApplierID,Applier)t1
left join
(select ApplierID as UnitID,Applier as Unit,count(0) as ReceivedQuantity
from PS_Orders
where IsDeleted=0 and ArriveTime is not null and IsReceived = 1
and ApplierDeptID='{0}' 
{1}
group by ApplierID,Applier)t2
on t1.UnitID=t2.UnitID
left join
(select ApplierID as UnitID,Applier as Unit,count(0) as RejectedQuantity
from PS_Orders
where IsDeleted=0 and ArriveTime is not null and IsReceived = 0
and ApplierDeptID='{0}' 
{1}
group by ApplierID,Applier)t3
on t1.UnitID=t3.UnitID", parentDeptID, condition);

            //再统计下级部门
            bool hasSubDepts = UserHelper.HasSubDeptments(parentDeptID);
            if (hasSubDepts)
            {
                sql += string.Format(@"
union all
select t1.UnitID,t1.Unit,t1.Quantity,t2.ReceivedQuantity,t3.RejectedQuantity,2 as UnitType
from
(select od.DeptID as UnitID,od.DeptName as Unit,count(0) as Quantity
from ORG_Deptments od
inner join v_Orders vo on charindex(od.DeptID,vo.ApplierDeptIDPath,0)>0
where IsDeleted=0 and InitialTime is not null and od.P_DeptID='{0}'
{1}
group by od.DeptID,od.DeptName)t1
left join
(select od.DeptID as UnitID,od.DeptName as Unit,count(0) as ReceivedQuantity
from ORG_Deptments od
inner join v_Orders vo on charindex(od.DeptID,vo.ApplierDeptIDPath,0)>0
where IsDeleted=0 and od.P_DeptID='{0}' 
and ArriveTime is not null and IsReceived = 1
{1}
group by od.DeptID,od.DeptName)t2
on t1.UnitID=t2.UnitID
left join
(select od.DeptID as UnitID,od.DeptName as Unit,count(0) as RejectedQuantity
from ORG_Deptments od
inner join v_Orders vo on charindex(od.DeptID,vo.ApplierDeptIDPath,0)>0
where IsDeleted=0 and od.P_DeptID='{0}' 
and ArriveTime is not null and IsReceived = 0
{1}
group by od.DeptID,od.DeptName)t3
on t1.UnitID=t3.UnitID

order by UnitType,Unit", parentDeptID, condition);
            }

            DataSet ds = DBHelper.Instance.ExecuteDataSet(CommandType.Text, sql);
            return ds;
        }


        /// <summary>
        /// 按项目类型统计各配送商的总数量、总金额、签收量、退件量
        /// </summary>
        public DataSet StatByProjectType(Actor actor,string projectTypeID, string condition)
        {
            //权限过滤
            condition += GetLimitCondition(actor);

            string sql = string.Format(@"
select t1.SupplierID,t1.Supplier,t1.Quantity,t2.ReceivedQuantity,t3.RejectedQuantity
from
(select SupplierID,Supplier,count(0) as Quantity
from PS_Orders
where IsDeleted=0 and InitialTime is not null and ProjectTypeID='{0}' 
{1}
group by SupplierID,Supplier)t1
left join
(select SupplierID,Supplier,count(0) as ReceivedQuantity
from PS_Orders
where IsDeleted=0 and ArriveTime is not null and IsReceived = 1 and ProjectTypeID='{0}' 
{1}
group by SupplierID,Supplier)t2
on t1.SupplierID=t2.SupplierID
left join
(select SupplierID,Supplier,count(0) as RejectedQuantity
from PS_Orders
where IsDeleted=0 and ArriveTime is not null and IsReceived = 0 and ProjectTypeID='{0}' 
{1}
group by SupplierID,Supplier)t3
on t1.SupplierID=t3.SupplierID
order by t1.SupplierID", projectTypeID, condition);

            DataSet ds = DBHelper.Instance.ExecuteDataSet(CommandType.Text, sql);
            return ds;
        }

        /// <summary>
        /// 按项目统计配送商某段时间每天的订单总量趋势
        /// </summary>
        /// <param name="projectTypeID"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataSet StatQuantityTrend(Actor actor,string projectTypeID,string condition)
        {
            //权限过滤
            condition += GetLimitCondition(actor);

            string sql = string.Format(@"
select SupplierID,Supplier,Year(InitialTime) as Year,Month(InitialTime) as Month,Day(InitialTime) as Day,count(0) as Quantity
from PS_Orders
where IsDeleted=0 and InitialTime is not null and ProjectTypeID='{0}'
{1}
group by SupplierID,Supplier,Year(InitialTime),Month(InitialTime),Day(InitialTime)
order by SupplierID,Year,Month,Day", projectTypeID, condition);

            DataSet ds = DBHelper.Instance.ExecuteDataSet(CommandType.Text, sql);
            return ds;
        }

        /// <summary>
        /// 获取权限过滤条件
        /// </summary>
        /// <param name="actor"></param>
        /// <returns></returns>
        private string GetLimitCondition(Actor actor)
        {
            if (actor is SupplierAccount)
            {
                SupplierAccount account = actor as SupplierAccount;
                return string.Format(" and SupplierID='{0}'", account.SupplierID);
            }
            return "";
        }

        /// <summary>
        /// 分类、分部门统计户数
        /// </summary>
        public DataSet StatByDeptAndType(string condition)
        {
            string sql = string.Format(@"
                  SELECT ab.DeptName,
                            (
                                SELECT COUNT(1)
                                FROM   AM_BaseInfo ab1
                                WHERE  ab1.DeptID = ab.DeptID
                                       AND ab1.[Type] = 'DW'  and ab1.IsDeleted=0 {0}
                            ) AS dwQuantity,
                            (
                                SELECT COUNT(1)
                                FROM   AM_BaseInfo ab1
                                WHERE  ab1.DeptID = ab.DeptID
                                       AND ab1.[Type] = 'GTH'  and ab1.IsDeleted=0 {0}
                            ) AS gthQuantity,
                            (
                                SELECT COUNT(1)
                                FROM   AM_BaseInfo ab1
                                WHERE  ab1.DeptID = ab.DeptID
                                       AND ab1.[Type] = 'GR'  and ab1.IsDeleted=0 {0}
                            ) AS grQuantity
                     FROM   AM_BaseInfo ab
                     WHERE  TYPE != 'ZH' and ab.IsDeleted=0  
                     GROUP BY
                            ab.DeptName,
                            ab.DeptID
                ", condition);

            DataSet ds = DBHelper.Instance.ExecuteDataSet(CommandType.Text, sql);
            return ds;
        }

        /// <summary>
        /// 按部门统计总参保户数占比
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataSet StatByDept(string condition)
        {
            string sql = string.Format(@"
                    SELECT t1.DeptName as Name,
                           t1.subtotal,
                           ROUND(CAST(t1.subtotal AS FLOAT) / t2.total, 2) * 100 AS rate
                    FROM   (
                               SELECT ab.DeptName,
                                      COUNT(0) AS subtotal
                               FROM   am_baseinfo ab
                               WHERE  TYPE!='ZH' and ab.IsDeleted=0  {0}
                               GROUP BY
                                      ab.DeptName
                           )t1,
                           (
                               SELECT COUNT(0) AS total
                               FROM   am_baseinfo od
                               WHERE  TYPE!='ZH'  and od.IsDeleted=0  {0}
                           )t2", condition);

            DataSet ds = DBHelper.Instance.ExecuteDataSet(CommandType.Text, sql);
            return ds;
        }

        /// <summary>
        /// 按参保类型
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataSet StatByInsurance(string condition)
        {
            string sql = string.Format(@"
                                    SELECT (
                                        SELECT COUNT(1)
                                        FROM   AM_BaseInfo abi
                                        WHERE  abi.Yanglao = 1 and abi.IsDeleted=0  {0}
                                    ) AS Yanglao,
                                    (
                                        SELECT COUNT(1)
                                        FROM   AM_BaseInfo abi
                                        WHERE  abi.Gongshang = 1  and abi.IsDeleted=0 {0}
                                    ) AS Gongshang,
                                    (
                                        SELECT COUNT(1)
                                        FROM   AM_BaseInfo abi
                                        WHERE  abi.Shiye = 1  and abi.IsDeleted=0 {0}
                                    ) AS Shiye,
                                    (
                                        SELECT COUNT(1)
                                        FROM   AM_BaseInfo abi
                                        WHERE  abi.Yiliao = 1  and abi.IsDeleted=0 {0}
                                    ) AS Yiliao,
                                    (
                                        SELECT COUNT(1)
                                        FROM   AM_BaseInfo abi
                                        WHERE  abi.Shengyu = 1  and abi.IsDeleted=0 {0}
                                    ) AS Shengyu", condition);

            DataSet ds = DBHelper.Instance.ExecuteDataSet(CommandType.Text, sql);
            return ds;
        }

        /// <summary>
        /// 按缴费单位类型
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataSet StatByPayType(string condition)
        {
            string sql = string.Format(@"
                    SELECT t1.PayUnitType as Name,
                           t3.subtotal,
                           t3.totalInsuranceCount,
                           ROUND(CAST(t3.subtotal AS FLOAT) / t2.total, 3) * 100 AS rate,
                           ROUND(CAST(t3.totalInsuranceCount AS FLOAT) / t2.totalCount, 3) * 100 AS rateInsuranceCount,
                            t1.ArchivesInfoCount,t1.PageCount
                    FROM   (
                               SELECT ab.PayUnitType,
                                       
                                       SUM(ab.InsuranceCount) AS totalInsuranceCount,
                                       SUM(CASE WHEN aa.BaseInfoID IS NULL THEN 0 ELSE 1 END) AS 
                                       ArchivesInfoCount,
                                       SUM(CASE WHEN aa.PageCount IS NULL THEN 0 ELSE aa.PageCount END) AS 
                                       PageCount
                                FROM   am_baseinfo ab
                                       LEFT OUTER JOIN AM_ArchivesInfo aa
                                            ON  aa.BaseInfoID = ab.BaseInfoID and aa.IsDeleted=0
                               WHERE  ab.TYPE!='ZH' and ab.IsDeleted=0 {0}
                               GROUP BY
                                      ab.PayUnitType
                           )t1,
                           (
                               SELECT COUNT(0) AS total,sum(od.InsuranceCount) as totalCount
                               FROM   am_baseinfo od
                               WHERE  TYPE!='ZH' and od.IsDeleted=0 {0}
                           )t2,
                            (
                                SELECT COUNT(0) AS subtotal,SUM(ab.InsuranceCount) AS totalInsuranceCount,
                                        ab.PayUnitType
                                FROM   am_baseinfo ab
                                WHERE  ab.TYPE != 'ZH'
                                        AND ab.IsDeleted = 0 {0}
                                GROUP BY
                                        ab.PayUnitType
                            ) t3
                            where t1.PayUnitType = t3.PayUnitType", condition);

            DataSet ds = DBHelper.Instance.ExecuteDataSet(CommandType.Text, sql);
            return ds;
        }

         /// <summary>
        /// 分类统计户数
        /// </summary>
        public DataSet StatByType(string condition)
        {
            string sql = string.Format(@"
                    SELECT case t1.TYPE when 'DW' then '单位'
                                when 'GTH' then '个体户'
                                 else '个人' 
                                end as Name,
                           t3.subtotal, t3.totalInsuranceCount,
                           ROUND(CAST(t3.subtotal AS FLOAT) / t2.total, 3) * 100 AS rate,
                            ROUND(CAST(t3.totalInsuranceCount AS FLOAT) / t2.totalCount, 3) * 100 AS rateInsuranceCount,
                            t1.ArchivesInfoCount,t1.PageCount
                    FROM   (
                               SELECT ab.TYPE as type,
                                       
                                       SUM(ab.InsuranceCount) AS totalInsuranceCount,
                                       SUM(CASE WHEN aa.BaseInfoID IS NULL THEN 0 ELSE 1 END) AS 
                                       ArchivesInfoCount,
                                       SUM(CASE WHEN aa.PageCount IS NULL THEN 0 ELSE aa.PageCount END) AS 
                                       PageCount
                                FROM   am_baseinfo ab
                                       LEFT OUTER JOIN AM_ArchivesInfo aa
                                            ON  aa.BaseInfoID = ab.BaseInfoID and aa.IsDeleted=0
                               WHERE  ab.TYPE!='ZH'  and ab.IsDeleted=0 {0}
                              GROUP BY
                                        ab.TYPE
                           )t1,
                           (
                               SELECT COUNT(0) AS total,sum(od.InsuranceCount) as totalCount
                               FROM   am_baseinfo od
                               WHERE  TYPE!='ZH'  and od.IsDeleted=0 {0}
                           )t2,(
                                SELECT COUNT(0) AS subtotal,SUM(ab.InsuranceCount) AS totalInsuranceCount,
                                        ab.TYPE
                                FROM   am_baseinfo ab
                                WHERE  ab.TYPE != 'ZH'
                                        AND ab.IsDeleted = 0 {0}
                                GROUP BY
                                        ab.TYPE
                            ) t3
                            where t1.TYPE = t3.TYPE                 
                ", condition);

            DataSet ds = DBHelper.Instance.ExecuteDataSet(CommandType.Text, sql);
            return ds;
        }

        /// <summary>
        /// 分类统计户数
        /// </summary>
        public string DengjiCount(string condition)
        {
            string sql = string.Format(@"
                    SELECT 
                                       SUM(CASE WHEN aa.BaseInfoID IS NULL THEN 0 ELSE 1 END) AS 
                                       DENGJICOUNT
                                FROM   am_baseinfo ab
                                       LEFT OUTER JOIN AM_ArchivesInfo aa
                                            ON  aa.BaseInfoID = ab.BaseInfoID and aa.Type='DJ' and aa.IsDeleted=0
                               WHERE  ab.TYPE!='ZH'  and ab.IsDeleted=0  {0}                
                ", condition);

            
            return DBHelper.Instance.ExecuteScalar(CommandType.Text, sql).ToString();
        }

        public string ZJCount(string condition)
        {
            string sql = string.Format(@"
                    SELECT 
                                       SUM(CASE WHEN aa.BaseInfoID IS NULL THEN 0 ELSE 1 END) AS 
                                       DENGJICOUNT
                                FROM   am_baseinfo ab
                                       LEFT OUTER JOIN AM_ArchivesInfo aa
                                            ON  aa.BaseInfoID = ab.BaseInfoID and aa.Type='ZJ' and aa.IsDeleted=0
                               WHERE  ab.TYPE!='ZH' and ab.IsDeleted=0   {0}                
                ", condition);

            
            return DBHelper.Instance.ExecuteScalar(CommandType.Text, sql).ToString();
        }
    }
}
